﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;

    public class RFInboundDA : IRFInboundDA
    {
        private string SQL_SELECT_RECEIPT_INFO_BY_LPNID = " SELECT DISTINCT R.WH_ID,R.RECEIPT_ID,R.RECEIPT_TYPE,R.RECEIPT_DATE,R.EXPECTED_RECEIPT_DATE,R.OWNER_ID,R.PO_ID, R.EXTERNAL_RECEIPT_ID,R.STATUS,R.ARRIVAL_DATE,R.VENDOR_ID,R.VENDOR_NAME,R.CARRIER_ID,R.CARRIER_NAME, R.REMARK,R.IS_PALLETIZED,IT.TO_LPN_ID AS LPN_ID FROM @_@RECEIPT R LEFT JOIN @_@LPN LPN ON R.RECEIPT_ID = LPN.DOC_ID AND LPN.DOC_TYPE = 'IN' LEFT JOIN @_@INV_TRANS IT ON IT.DOC_ID = R.RECEIPT_ID AND IT.TRANS_TYPE = 'IN' AND IT.TO_LPN_ID = @LPN_ID  WHERE RIGHT(LPN.LPN_ID,4) = '0000' AND LPN.LPN_ID = @LPN_ID ";
        private string SQL_SELECT_RF_RECEIPT_VALIDATION_INFO = "SELECT SKU.OWNER_ID,SKU.SKU_ID,SKU.LOT_TEMPLATE_ID,SKU.DESCR AS SKU_DESCR,SKU.PACK_ID,RD.REMARK AS RECEIPT_TYPE,SUM(RD.EXPECTED_QTY) AS TOTAL_EXPECTED_QTY, (SELECT SUM(RECEIVED_QTY) FROM @_@RECEIPT_DETAIL WHERE RECEIPT_ID = @RECEIPT_ID AND SKU_ID =RD.SKU_ID) AS TOTAL_RECEIVED_QTY , COMMODITY_NOT_ON_ASN,IS_QTY_VALIDATION,OVERAGE_PERCENT,RD.SKU_ID AS RF_SKU_ID FROM @_@SKU SKU LEFT JOIN @_@RECEIPT_DETAIL RD ON RD.OWNER_ID = SKU.OWNER_ID AND RD.SKU_ID = SKU.SKU_ID AND RD.RECEIPT_ID = @RECEIPT_ID LEFT JOIN @_@RECEIPT_VALIDATION RV ON SKU.RECEIPT_VALIDATION_ID = RV.RECEIPT_VALIDATION_ID WHERE SKU.OWNER_ID = @OWNER_ID AND (SKU.B_UDF1 = @SKU_BAR_CODE OR SKU.B_UDF2 = @SKU_BAR_CODE OR SKU.B_UDF3 = @SKU_BAR_CODE OR SKU.B_UDF4 = @SKU_BAR_CODE OR SKU.B_UDF5 = @SKU_BAR_CODE OR SKU.B_UDF6 = @SKU_BAR_CODE OR SKU.B_UDF7 = @SKU_BAR_CODE OR SKU.B_UDF8 = @SKU_BAR_CODE OR SKU.B_UDF9 = @SKU_BAR_CODE OR SKU.B_UDF10 = @SKU_BAR_CODE )  GROUP BY SKU.OWNER_ID,SKU.SKU_ID,SKU.LOT_TEMPLATE_ID,SKU.DESCR,SKU.PACK_ID,RD.REMARK,COMMODITY_NOT_ON_ASN,IS_QTY_VALIDATION,OVERAGE_PERCENT,RD.SKU_ID";

        public RFReceiptInfo GetRFReceiptInfoByLpnID(string lpnID, string whLoginID)
        {
            RFReceiptInfo rfReceiptInfo = null;
            if (!string.IsNullOrEmpty(lpnID))
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LPN_ID", lpnID) };
                using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, this.SQL_SELECT_RECEIPT_INFO_BY_LPNID.Replace("@_@", whLoginID), paras))
                {
                    if (reader.Read())
                    {
                        DateTime CS00003;
                        rfReceiptInfo = new RFReceiptInfo(reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_TYPE"].ToString(), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECTED_RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), reader["EXTERNAL_RECEIPT_ID"].ToString(), reader["STATUS"].ToString(), (reader["ARRIVAL_DATE"].ToString() != "") ? DateTime.Parse(reader["ARRIVAL_DATE"].ToString()) : new DateTime(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["REMARK"].ToString(), reader["IS_PALLETIZED"].ToString(), reader["LPN_ID"].ToString());
                    }
                }
            }
            return rfReceiptInfo;
        }

        public RFReceiptValidationInfo GetRFReceiptValidationByAsnIDAndSkuBarCode(string receiptID, string ownerID, string skuBarCode, string whLoginID)
        {
            RFReceiptValidationInfo rfReceiptValidationInfo = null;
            if ((!string.IsNullOrEmpty(receiptID) && !string.IsNullOrEmpty(ownerID)) && !string.IsNullOrEmpty(skuBarCode))
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_BAR_CODE", skuBarCode), new SqlParameter("@RECEIPT_ID", receiptID) };
                using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, this.SQL_SELECT_RF_RECEIPT_VALIDATION_INFO.Replace("@_@", whLoginID), paras))
                {
                    if (reader.Read())
                    {
                        rfReceiptValidationInfo = new RFReceiptValidationInfo(reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["PACK_ID"].ToString(), reader["RECEIPT_TYPE"].ToString(), (reader["TOTAL_EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_EXPECTED_QTY"].ToString()) : 0M, (reader["TOTAL_RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_RECEIVED_QTY"].ToString()) : 0M, reader["COMMODITY_NOT_ON_ASN"].ToString(), reader["IS_QTY_VALIDATION"].ToString(), (reader["OVERAGE_PERCENT"].ToString() != "") ? decimal.Parse(reader["OVERAGE_PERCENT"].ToString()) : 0M, reader["RF_SKU_ID"].ToString(), reader["LOT_TEMPLATE_ID"].ToString());
                    }
                }
            }
            return rfReceiptValidationInfo;
        }
    }
}

